import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

public class CopyUtils {

    public void copyWbSheet(String srcfileName, String destFileName,
                            String sheetName, String destSheetName) throws Exception {
        File srcFile = new File(srcfileName);
        if (!srcFile.exists()) {
            return;
        }
        HSSFWorkbook srcwb = new HSSFWorkbook(new FileInputStream(srcfileName));
        HSSFSheet srcSheet = null;
        if (sheetName == null) {
            srcSheet = srcwb.getSheetAt(0);
            sheetName = srcwb.getSheetName(0);
        } else {
            srcSheet = srcwb.getSheet(sheetName);
        }
        if (srcSheet == null) {
            return;
        }
        if (destSheetName == null) {
            destSheetName = sheetName;
        }
        HSSFWorkbook destwb = new HSSFWorkbook();
        HSSFSheet destSheet = null;
        File destFile = new File(destFileName);
        // 不存在则新建
        if (!destFile.exists()) {
            destFile.createNewFile();
        } else {
            destwb = new HSSFWorkbook(new FileInputStream(destFile));
        }
        destSheet = destwb.getSheet(destSheetName);
        if (destSheet == null) {
            destSheet = destwb.createSheet(destSheetName);
        }
        // 最大列数
        int maxCellNum = copySheet(srcSheet, destSheet, srcwb, destwb);
        // 设置列宽
        setSheetWidth(srcSheet, destSheet, maxCellNum);
        // 合并单元格
        mergeSheetAllRegion(srcSheet, destSheet);
        // 保存
        saveFile(destwb, destFileName);
    }

    public void saveFile(HSSFWorkbook destwb, String destFileName) {
        try {
            FileOutputStream fileOutStream = new FileOutputStream(destFileName);
            destwb.write(fileOutStream);
            if (fileOutStream != null) {
                fileOutStream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public int copySheet(HSSFSheet srcSheet, HSSFSheet destSheet,
                         HSSFWorkbook srcwb, HSSFWorkbook destwb) throws Exception {
        int rowCount = srcSheet.getLastRowNum();// 总行数
        int maxCellNum = 0;
        // System.out.println("------total row=------" + rowCount + "---="+
        // srcSheet.getPhysicalNumberOfRows());
        HSSFRow srcRow = null, destRow = null;
        //注意这里
        for (int i = 0; i <=rowCount; i++) {
            srcRow = srcSheet.getRow(i);
            destRow = destSheet.getRow(i);
            if (srcRow == null) {
                continue;
            }
            // 最大列数
            maxCellNum = maxCellNum < srcRow.getLastCellNum() ? srcRow
                    .getLastCellNum() : maxCellNum;
            if (destRow == null) {
                destRow = destSheet.createRow(i);
            }
            // 设置行高
            destRow.setHeight(srcRow.getHeight());
            // System.out.println("---------row=" + i + "---="+
            // srcRow.getPhysicalNumberOfCells() +
            // "----="+srcRow.getLastCellNum());
            copySheetRow(srcRow, destRow, srcwb, destwb);
            srcRow = null;
            destRow = null;
        }
        srcRow = null;
        destRow = null;
        return maxCellNum;
    }

    private void copySheetRow(HSSFRow srcRow, HSSFRow destRow,
                              HSSFWorkbook srcwb, HSSFWorkbook destwb) {
        int cellCount = srcRow.getLastCellNum();// 每行的总列数
        HSSFCell srcCell = null, destCell = null;
        HSSFCellStyle srcCellStyle = null, destCellStyle = null;
        for (int j = 0; j < cellCount; j++) {   // 遍历行单元格
            srcCell = srcRow.getCell(j);
            destCell = destRow.getCell(j);
            if (destCell == null) {
                destCell = destRow.createCell(j);
            }
            if (srcCell != null) {
                srcCellStyle = srcCell.getCellStyle();      // 原sheet页样式
                destCellStyle = destCell.getCellStyle();
                // 复制样式
//                destCellStyle.cloneStyleFrom(srcCellStyle);
                // copyCellStyle(srcCellStyle,destCellStyle);
                destCell.setCellStyle(srcCellStyle);
                // 处理单元格内容
                switch (srcCell.getCellType()) {
                    case HSSFCell.CELL_TYPE_STRING:
                        destCell.setCellValue(srcCell.getRichStringCellValue());
                        break;
                    // 这里判断是否是日期
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        // 判断是否是日期格式
                        // 测试发现如果这里不新建样式,日期显示的是数字
                        if (DateUtil.isCellDateFormatted(srcCell)) {
                            // 新建样式
                            destCellStyle = destwb.createCellStyle();
                            // 复制样式
                            destCellStyle.cloneStyleFrom(srcCellStyle);
                            destCell.setCellStyle(destCellStyle);
                            destCell.setCellValue(srcCell.getDateCellValue());
                        } else {
                            destCell.setCellValue(srcCell.getNumericCellValue());
                        }
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        destCell.setCellFormula(srcCell.getCellFormula());
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        destCell.setCellValue(srcCell.getBooleanCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_BLANK:
                        destCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:
                        break;
                    default:
                        break;
                }
            }
        }
        srcCellStyle = null;
        destCellStyle = null;
        srcCell = null;
        destCell = null;
    }

    public void mergeSheetAllRegion(HSSFSheet srcSheet, HSSFSheet destSheet) {
        int num = srcSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = srcSheet.getMergedRegion(i);
            destSheet.addMergedRegion(cellR);
        }
    }

    public void setSheetWidth(HSSFSheet srcSheet, HSSFSheet destSheet,
                              int maxCellNum) {
        for (int i = 0; i <= maxCellNum; i++) {
            destSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
        }
    }

    /**
     * 复制一个单元格样式到目的单元格样式
     * @param fromStyle
     * @param toStyle
     */
    public static void copyCellStyle(HSSFCellStyle fromStyle,
                                     HSSFCellStyle toStyle) {
        toStyle.setAlignment(HorizontalAlignment.forInt(fromStyle.getAlignment()));
        //边框和边框颜色
        toStyle.setBorderBottom(BorderStyle.valueOf(fromStyle.getBorderBottom()));
        toStyle.setBorderLeft(BorderStyle.valueOf(fromStyle.getBorderLeft()));
        toStyle.setBorderRight(BorderStyle.valueOf(fromStyle.getBorderRight()));
        toStyle.setBorderTop(BorderStyle.valueOf(fromStyle.getBorderTop()));
        toStyle.setTopBorderColor(fromStyle.getTopBorderColor());
        toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor());
        toStyle.setRightBorderColor(fromStyle.getRightBorderColor());
        toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor());

        //背景和前景
        toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor());
        toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor());

        toStyle.setDataFormat(fromStyle.getDataFormat());
        toStyle.setFillPattern(FillPatternType.forInt(fromStyle.getFillPattern()));
//		toStyle.setFont(fromStyle.getFont(null));
        toStyle.setHidden(fromStyle.getHidden());
        toStyle.setIndention(fromStyle.getIndention());//首行缩进
        toStyle.setLocked(fromStyle.getLocked());
        toStyle.setRotation(fromStyle.getRotation());//旋转
        toStyle.setVerticalAlignment(VerticalAlignment.forInt(fromStyle.getVerticalAlignment()));
        toStyle.setWrapText(fromStyle.getWrapText());

    }

}
